/**
 * 
 */
package parse;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * <p>Title:ParseExcelToBean</p>
 * <p>Description:TODO</p>
 * @author	xiongf
 * @date	2016年1月25日 上午10:31:34
 * @version 1.0
 */
public class ParseExcelToBean {

	public static List<List<RowVO>> excleTOVO(File exceFile) throws Exception {
		String errorMsg = "";
		/*try {
			url = URLDecoder.decode(url, "utf-8"); // 防止服务器路径中包含空格等问题
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		String suffix = url.substring(url.lastIndexOf(".")); // 文件后辍.
		File file = new File(url);*/
		String suffix = exceFile.getName().substring(exceFile.getName().lastIndexOf(".")); // 文件后辍.
		List<List<RowVO>> excelSheets = null;
		if (exceFile != null && exceFile.exists()) {
			try {
				Workbook workBook = null;
				InputStream is = new FileInputStream(exceFile);
				try {
					if (".xls".equals(suffix)) { // 97-03
						workBook = new HSSFWorkbook(is);
					} else if (".xlsx".equals(suffix)) { // 2007
						workBook = new XSSFWorkbook(is);
					} else {
						throw new Exception("不支持的文件类型！");
					}
				} catch (Exception e) {
					throw new Exception("解析excel文件出错！");
				} finally {
					try {
						is.close();
					} catch (Exception e2) {

					}
				}
				int sheets = null != workBook ? workBook.getNumberOfSheets() : 0;
				if (sheets > 0) {
					excelSheets = new ArrayList<List<RowVO>>();
					for (int i = 0; i < 1; i++) {
						Sheet sheet = workBook.getSheetAt(i); // 读取第一个sheet
						int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
						List<RowVO> sheetList = new ArrayList<RowVO>();
						if (rows > 1) { // 第一行默认为标题
							// sheet.getMargin(HSSFSheet.TopMargin);
							for (int j = 1; j < rows; j++) {
								Row row = sheet.getRow(j);
								RowVO commonVO = new RowVO();
								int cells = row.getLastCellNum();// 获得列数
								if (cells != 2) {
									throw new Exception("文档列数不为2");
								}

								if (cells > 0) {
									for (int k = 0; k < cells; k++) {
										Cell cell = row.getCell(k);
										if (!NumberUtils.isNumber(getStringCellValue(cell))) {
											errorMsg = "第" + (j + 1) + "行数据存在无效的数据";
											throw new Exception(errorMsg);
										}
										// cell.setCellType(Cell.CELL_TYPE_STRING);
										// // 全部置成String类型的单元格
										/*String cellValue = "";
										if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
											cellValue = cell.getNumericCellValue() + "";
										} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
											cellValue = cell.getStringCellValue();
										}*/
										if (k == 0) {
											PropertyUtils.setProperty(commonVO, "custno",
											        getStringCellValue(cell));
										}
										if (k == 1) {
											PropertyUtils.setProperty(commonVO, "acctno",
											        getStringCellValue(cell));
										}
									}
								} else {
									errorMsg = "第" + (j + 1) + "行数据没有列数为空!";
									throw new Exception(errorMsg);
								}
								sheetList.add(commonVO);
							}
						} else {
							errorMsg = "第" + (i + 1) + "个sheet中数据行数<=1";
							throw new Exception(errorMsg);
						}
						excelSheets.add(sheetList);
					}
				} else {
					errorMsg = "没有sheet!";
					throw new Exception(errorMsg);
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				throw ex;
			}
		} else {
			errorMsg = "未正确选择文件或文件不存在！";
		}
		if (errorMsg.length() > 0) {
			System.out.println("错误消息：" + errorMsg);
		}
		return excelSheets;
	}

	public static void main(String[] args) throws Exception {
		File fileName = new File("D:\\test.xlsx");
		List<List<RowVO>> excelSheets = ParseExcelToBean.excleTOVO(fileName);
		if (null != excelSheets) {
			for (int i = 0; i < excelSheets.size(); i++) {
				List<RowVO> sheet = excelSheets.get(i);
				for (int j = 0; j < sheet.size(); j++) {
					RowVO commonVO = sheet.get(j);
					System.out.println(commonVO.getCustno() + "|" + commonVO.getAcctno());
				}
			}
		}
	}

	
	/**
	 * 读取excel值
	 * @param cell
	 * @return
	 */
	private static String getStringCellValue(Cell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				strCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				strCell = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				strCell = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strCell = "";
				break;
			default:
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

}
